var models = require('../db');
var express = require('express');
var router = express.Router();
var mysql = require('mysql');
var $sql = require('../sql');

// 连接数据库
var conn = mysql.createConnection(models.mysql);

conn.connect();

// 查询违纪记录
router.post('/queryAllVioList', (req, res) => {
  var sql = $sql.violation.queryAllVioList;
  let content = [];
  let isMore = false;
  if (req.body.query !== ''){
    sql +=" where vioType = ?"
    content.push( req.body.query )
    isMore =true
  }
  // 姓名
  if(req.body.queryList.name !== ''){
    if (isMore){
      sql += " and "
    }else {
      sql +=" where "
    }
    isMore = true;
    sql += "studentName LIKE ?";
    content.push( "%"+req.body.queryList.name+"%" );
  }
  // 学号
  if(req.body.queryList.studentId !== ''){
    if (isMore){
      sql += " and "
    }else {
      sql +=" where "
    }
    isMore = true;
    sql += "studentId LIKE ?";
    content.push( "%"+req.body.queryList.studentId+"%" );
  }
  // 楼号
  if(req.body.queryList.buildNo !== ''){
    if (isMore){
      sql += " and "
    }else {
      sql +=" where "
    }
    isMore = true;
    sql += "buildNo LIKE ?";
    content.push( "%"+req.body.queryList.buildNo+"%" );
  }
  // 宿舍号
  if(req.body.queryList.dormNo !== ''){
    if (isMore){
      sql += " and "
    }else {
      sql +=" where "
    }
    isMore = true;
    sql += "dormNo LIKE ?";
    content.push( "%"+req.body.queryList.dormNo+"%" );
  }
  // 辅导员
  if(req.body.queryList.counselor !== ''){
    if (isMore){
      sql += " and "
    }else {
      sql +=" where "
    }
    isMore = true;
    sql += "counselor LIKE ?";
    content.push( "%"+req.body.queryList.counselor+"%" );
  }
  // 开始时间
  if(req.body.queryList.startTime !== '' && req.body.queryList.endTime !== ''){
    if (isMore){
      sql += " and "
    }else {
      sql +=" where "
    }
    isMore = true;
    sql += "date > ? and date < ? ";
    content.push( req.body.queryList.startTime );
    content.push( req.body.queryList.endTime );
  }
  isMore = false;
  conn.query(sql, content, function (err, result) {
    var data = JSON.parse(JSON.stringify(result))
    if (err) {
      return res.send({
        code: 1,
        message: '查询失败'
      })
    }else{
      // nodejs循环处理时间格式
      data.forEach(function(item,index){
        var moment = require('moment');
        moment.locale('zh-cn');
        data[index].date =moment(data[index].date).format('YYYY-MM-DD HH:mm:ss'); 
      }) 
          return res.send({
            status: 1,
            message: "success",
            list: data,
            
      })
    }
  })
});
// 新增违纪
router.post('/addVio', (req, res) => {
  var sql = $sql.violation.addVio;
  console.log(sql);
  console.log(req.body);
  conn.query(sql, [req.body.date,req.body.studentId,req.body.studentName,req.body.buildNo,req.body.dormNo,req.body.bedNo,req.body.professional,req.body.counselor,req.body.vioType,req.body.vioThing,req.body.vioNum,req.body.commitMan,req.body.remarks], function (err, result) {
    // var data = JSON.parse(JSON.stringify(result))
    return res.send({
      status: 1,
      msg: "新增成功",
      // list: data
    })
  })
});

router.post('/vioStudent', (req, res) => {
  var sql = $sql.violation.vioStudent;
  conn.query(sql, [req.body.buildNo,req.body.dormNo,req.body.bedNo], function (err, result) {
    var data = JSON.parse(JSON.stringify(result))
    return res.send({
      status: 200,
      msg: "查询成功",
      list: data
    })
  })
});
// 查询违纪物品列表
router.post("/vioThingsList",(req,res)=>{
  var sql = $sql.violation.vioThingsList;
  conn.query(sql,[],function(err,result){
    var data = JSON.parse(JSON.stringify(result))
    return res.send({
      status: 200,
      msg: "查询成功",
      list: data
    })
  })
}),
// 修改编辑
router.post("/editVio",(req,res)=>{
  var sql = $sql.violation.editVio;
  console.log(sql);
  console.log(req.body);
  conn.query(sql,[req.body.remarks,req.body.istrue,req.body.date,req.body.studentId,req.body.vioType],function(err,result){
    // var data = JSON.parse(JSON.stringify(result))
    return res.send({
      status: 200,
      msg: "修改成功",
    })
  })
}),

router.post("/samedorm",(req,res)=>{
  var sql = $sql.violation.samedorm;
  console.log(sql);
  console.log(req.body);
  conn.query(sql,[req.body.userId,req.body.userId],function(err,result){
    var list = JSON.parse(JSON.stringify(result))
    return res.send({
      status: 200,
      msg: "修改成功",
      data:list
    })
  })
}),
module.exports = router;
